<?php
namespace App\Admin\Controllers;
use Encore\Admin\Layout\Content;
use Encore\Admin\Facades\Admin;
use App\Models\Class_section_group;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use  App\Admin\Controllers\Common\ReportBase;
class Report_lossController extends ReportBase {
	public function __construct() {
		$term = DB::table("terms")->orderBy("id",'desc')->first();
		parent::__construct($term->year,$term->season);
	}

	/**
	 * loss_report
	 */
	public function index(Content $content) {
		return Admin::content(function (Content $content){
			$opt_start = [];
			for($i=0;$i<$this->term;$i++){
				$opt_start[] = [
					'label' => "第".($i+1)."周",
					'value' => $i
				];
			}
			$opt_end = [];
			for($i=$this->term;$i>0;$i--){
				$opt_end[] = [
					'label' => "第".($i)."周",
					'value' => $i
				];
			}
			$default_week = [
				'start' => $this->term-4,
				'end' => $this->term-1
			];
            $content->header(trans('LossReport'));
            $content->description(trans('流失报表'));
			$content->body(view("admin.loss.index",[
				'opt_end' => $opt_end,
				'opt_start' => $opt_start,
				'default_week' => $default_week,
				'default_year' => date("Y"),
				'default_season' => 1
			]));
        });
	}

	public function getOverviewTable(Request $request) {
		$start_week = $request->get('start_week');
		$end_week = $request->get('end_week');
 		return ['code'=>1,'msg'=>'ok','data'=>$this->mkWeekLossArr($start_week,$end_week)];
 	}


	public function mkWeekLossArr($start_week,$end_week) {
		$stage1_id = DB::table('class_section_groups')
			->where([
				['type','=','1'],
				['pid','=','0'],
				['name','=','一段班']
			])->value("id");
 		$stage2_id = DB::table('class_section_groups')
			->where([
				['type','=','1'],
				['pid','=','0'],
				['name','=','二段班']
			])->value("id");
 		$group1_ids = DB::table('class_section_groups as group')
			->where([
				['type','=','2'],['pid','=',$stage1_id]
			])->pluck("id");
 		$group2_ids = DB::table('class_section_groups')
			->where([
				['type','=','2'],['pid','=',$stage2_id]
			])->pluck("id");
 		$squad1_ids = DB::table('squads')->whereIn('group',$group1_ids)->pluck("id");
 		$squad2_ids = DB::table('squads')->whereIn('group',$group2_ids)->pluck("id");
 		$count1 = DB::table("students")->whereIn('squad_id',$squad1_ids)->count();
 		$count2 = DB::table("students")->whereIn('squad_id',$squad2_ids)->count();
		$arr = [];
 		for($i=$start_week;$i<$end_week;$i++){
			$start_time = date("Y-m-d",($this->start_timestamp + $i*(7*24*3600)))." 00:00:00";
 			$end_time = date("Y-m-d H:i:s",(strtotime($start_time) + 7 * 24 * 3600));
			$total1 = DB::table("students")
				->where("enrollment_time","<=",$end_time)
				->whereIn('squad_id',$squad1_ids)
				->count();
			$total1 -= DB::table("applys")
				->where("status","=","1")
				->whereIn("type",["2","3"])
				->whereIn('squad_id',$squad1_ids)
				->where("sp_time","<=",$end_time)
				->count();
			$total1 += DB::table("applys")
				->where([
					["status","=","1"],
					["type","=","4"],
					["sp_time","<=",$end_time]
				])
				->whereIn('squad_id',$squad1_ids)
				->count();
			$total2 = DB::table("students")
				->where("enrollment_time","<=",$end_time)
				->whereIn('squad_id',$squad2_ids)
				->count();
			$total2 -= DB::table("applys")
				->where("status","=","1")
				->whereIn("type",["2","3"])
				->whereIn('squad_id',$squad2_ids)
				->where("sp_time","<=",$end_time)
				->count();
			$total2 += DB::table("applys")
				->where([
					["status","=","1"],
					["type","=","4"],
					["sp_time","<=",$end_time]
				])
				->whereIn('squad_id',$squad2_ids)
				->count();
			$lost1 = DB::table('applys')
				->where([
					['status','=','1'],
					['sp_time','>',$start_time],
					['sp_time','<=',$end_time]
				])
				->whereIn('type',['2','3'])
				->whereIn('squad_id',$squad1_ids)
				->count();
			$lost2 = DB::table('applys')
				->where([
					['status','=','1'],
					['sp_time','>',$start_time],
					['sp_time','<=',$end_time]
				])
				->whereIn('type',['2','3'])
				->whereIn('squad_id',$squad2_ids)
				->count();
			$arr[$i] = [
 				"week" => "第".($i+1)."周",
 				"total_1" => $total1,
 				"total_2" => $total2,
 				"lost_1" => $lost1,
 				"lost_2" => $lost2,
 			];
 		}
		$arr = array_merge($arr);
 		foreach($arr as $k => &$v){
 			$v['total_1'] == 0 ?
 			 	$v['rate_1'] = "- / -" :
 				$v['rate_1'] = round($v['lost_1'] / $v['total_1'] * 100,2)."%";
 			$v['total_2'] == 0 ?
 				$v['rate_2'] = "- / -" :
 				$v['rate_2'] = round($v['lost_2'] / $v['total_2'] * 100,2)."%";
 			$lost_0 = $v['lost_1'] + $v['lost_2'];
 			if($lost_0){
 				$v['rate_1_week'] = round(($v['lost_1']/$lost_0),4);
 				$v['rate_2_week'] = 100*(abs(1 - $v['rate_1_week']))."%";
 				$v['rate_1_week'] = 100*($v['rate_1_week'])."%";
 			}else{
 				$v['rate_2_week'] =  $v['rate_1_week'] = "- / -";
 			}
 		}
		return $arr;
	}

	/**
	 * 开办段统计
	 */
	public function getStartlessonTable(Request $request) {
		$rows = DB::table('student_losts')
			->where(['term_year'=>(int)$request->get('year'),'term_season'=>(int)$request->get('season')])
			->where('week',">",(int)$request->get('start_week'))
			->where('week',"<=",(int)$request->get('end_week'))
			->groupBy('squad_batch')
			->orderBy('lost','desc')
			->get(['squad_batch',DB::raw('sum(lost_students) as lost')]);
		$rows = json_decode(json_encode($rows),true);
		$total = sizeof($rows);
		return ['code'=>1,'msg'=>'ok','data'=>['rows'=>$rows,'total'=>$total]];
	}

	/**
	 * 班级统计
	 */
	public function getLessonTable(Request $request) {
		$rows = DB::table('student_losts')
			->where('term_year',"=",(int)$request->get('year'))
			->where('term_season',"=",(int)$request->get('season'))
			->where('week',">",(int)$request->get('start_week'))
			->where('week',"<=",(int)$request->get('end_week'))
			->groupBy('squad_name')
			->orderBy('lost','desc')
			->get(['squad_name as name',DB::raw('sum(lost_students) as lost'),'tutor_name']);
		$rows = json_decode(json_encode($rows),true);
		$total = sizeof($rows);
		return ['code'=>1,'msg'=>'ok','data'=>['rows'=>$rows,'total'=>$total]];
	}

	/**
	 * 班主任统计
	 */
	public function getTutorTable(Request $request) {
		$rows = DB::table("student_losts")
			->where(['term_year'=>(int)$request->get('year'),'term_season'=>(int)$request->get('season')])
			->where('week',">",(int)$request->get('start_week'))
			->where('week',"<=",(int)$request->get('end_week'))
			->groupBy('class_teacher')->orderBy('lost','desc')->get(['tutor_name',DB::raw('sum(lost_students) as lost')]);
		$rows = json_decode(json_encode($rows),true);
		$total = sizeof($rows);
		return ['code'=>1,'msg'=>'ok','data'=>['rows'=>$rows,'total'=>$total]];
	}

	/**
	 * 章节统计
	 */
	public function getChapterTable(Request $request) {

	}
}
